﻿using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using System.Linq;
using System.Web;
using System.Data;
using System.Diagnostics;
using Gollie.WebCache;
using MySQL.Db;
using MySql.Data;
using MySql.Data.MySqlClient;
using NLog;

namespace Gollie.Database
{
    public static class Queries
    {
		private static Logger logger = LogManager.GetCurrentClassLogger();

        //resturns the hashed userid to identify the user in the activatin table
         internal static string RegisterUser(string user, int roleId, string hashedPassword, string email, string initials, string infix, string lastName, bool sex, string city, DateTime birthday, string mobile, int vendorid, string hashedPincode)
        {
            QueryArguments args = new QueryArguments();

            args.Add("p_username", user);
            args.Add("p_roleid", roleId);
            args.Add("p_password", hashedPassword);
            args.Add("p_email", email);
            args.Add("p_initials", initials);
            args.Add("p_infix", infix);
            args.Add("p_lastname", lastName);
            args.Add("p_sex", sex);
            args.Add("p_city", city);
            args.Add("p_birthdate", birthday);
            args.Add("p_mobile", mobile);
            args.Add("p_vendorid", vendorid);
            args.Add("p_hashedusername", "").Direction = ParameterDirection.Output;
			args.Add("p_pincode", hashedPincode);

            Query q = new Query("sp_registeruser", args);

            q.Execute();

            return args[12].Value.ToString();
        }

        internal static string ActivateUser(string hashedUser)
        {
			//returns the userid, or null if not found
            Query q = new Query("sp_activateuser", "p_hashedusername", hashedUser);

			return (string)q.Execute();
        }

		//register and activate immediately.
		internal static string RegisterVendorUser(string user, int roleId, string hashedPassword, string email, string initials, string infix, string lastName, bool sex, string city, DateTime birthday, string mobile, int vendorid)
		{
			string hashed = RegisterUser(user, roleId, hashedPassword, email, initials, infix, lastName, sex, city, birthday, mobile, vendorid, null);

			return ActivateUser(hashed);
		}

		internal static ResultSet Login(string user, string pw)
		{
			ClearUserCache(user);

			QueryArguments args = new QueryArguments();
			args.Add("p_username", user);
			args.Add("p_password", pw);

			Query query = new Query("sp_login", args);

			ResultSet r = null;

			try
			{
				r = (ResultSet)query.Execute();
			}
			catch (MySqlException ex)
			{
				logger.FatalException("Exception during login " + user, ex); 
				if (ex.Number == 255)
				{
					Global.HackAttempt();
				}
			}

			return r;
		}

		internal static UserInfo GetUser(int userId, bool hasToBeActivated)
		{
			string key = "data_userid_" + userId.ToString();
			UserInfo u = DataCache.Cache[key] as UserInfo;

			if (u == null)
			{
				QueryArguments args = new QueryArguments();
				args.Add("p_username", (string)null);
				args.Add("p_userid", userId);
				args.Add("p_hastobeactivated", hasToBeActivated);

				Query query = new Query("sp_getuser", args);

				ResultSet r = (ResultSet)query.Execute();
				u = GetUser(r);
			}

			return u;
		}

		internal static UserInfo GetUser(string user, bool hasToBeActivated)
		{
			string key = "data_username_" + user;
			UserInfo u = DataCache.Cache[key] as UserInfo;

			if (u == null)
			{
				QueryArguments args = new QueryArguments();
				args.Add("p_username", user);
				args.Add("p_userid", (uint?)null);
				args.Add("p_hastobeactivated", hasToBeActivated);

				Query query = new Query("sp_getuser", args);

				ResultSet r = (ResultSet)query.Execute();
				u = GetUser(r);
			}

			return u;
		}

		private static UserInfo GetUser(ResultSet r)
		{
			UserInfo u = null;

			if (r.Count > 0)
			{
				u = new UserInfo(r);
			
				string usernameKey = "data_username_" + u.Gebruikersnaam;
				DataCache.Insert(usernameKey, u, DateTime.Now.AddMinutes(30));

				string userIdKey = "data_userid_" + u.Id.ToString();
				DataCache.Insert(userIdKey, u, DateTime.Now.AddMinutes(30));
			}

			return u;
		}

		internal static VendorInfo GetVendor(uint vendorId)
		{
			string key = "data_vendorname_" + vendorId.ToString();
			VendorInfo v = DataCache.Cache[key] as VendorInfo;

			if (v == null)
			{
				Query query = new Query("sp_getvendor", "p_vendorid", vendorId);

				ResultSet r = (ResultSet)query.Execute();
				if (r.Count > 0)
				{
					v = new VendorInfo(r);
					DataCache.Insert(key, v, DateTime.Now.AddMinutes(30));
				}
			}

			return v;
		}

		internal static void ClearVendorCache(uint vendorId)
		{
			string key = "data_vendorname_" + vendorId.ToString();

			DataCache.Cache.Remove(key);
		}

		private static void ClearUserCache(string user)
		{
			string key = "data_username_" + user;

			UserInfo u = DataCache.Cache[key] as UserInfo;
			if (u != null)
			{
				DataCache.Cache.Remove(key);
				DataCache.Cache.Remove("data_userid_" + u.Id.ToString());
			}
		}

		internal static int NewPassword(string user, string newpassword)
		{
			ClearUserCache(user);

			QueryArguments args = new QueryArguments();
			args.Add("p_username", user);
			args.Add("p_newpassword", newpassword);

			Query query = new Query("sp_newpassword", args);

			return (int)query.Execute();
		}

		internal static int UpdateUser(string user, string password, string email, string initials, string infix, string lastName, bool sex, string city, DateTime birthday, string mobile, string newpassword, string newpincode)
		{
			ClearUserCache(user);

			QueryArguments args = new QueryArguments();

			args.Add("p_username", user);
			args.Add("p_password", password);
			args.Add("p_email", email);
			args.Add("p_initials", initials);
			args.Add("p_infix", infix);
			args.Add("p_lastname", lastName);
			args.Add("p_sex", sex);
			args.Add("p_city", city);
			args.Add("p_birthdate", birthday);
			args.Add("p_mobile", mobile);
			args.Add("p_newpassword", newpassword);
			args.Add("p_newpincode", newpincode);

			Query query = new Query("sp_updateuser", args);

			return (int)query.Execute();
		}

		internal static int UpdateVendorSettings(uint vendorId, bool allowrefund)
		{
			QueryArguments args = new QueryArguments();

			args.Add("p_vendorid", vendorId);
			args.Add("p_allowrefund", allowrefund);

			Query query = new Query("sp_updatevendorsettings", args);

			int retval = (int)query.Execute();

			ClearVendorCache(vendorId);

			var toRemove = DataCache.Cache.Where(x => x.Value is UserInfo && ((UserInfo)x.Value).VendorId == vendorId).Select(x => x.Value);
			foreach (UserInfo ui in toRemove)
			{
				ui.VendorInfo = GetVendor(vendorId);
			}

			return retval;
		}

		internal static string RegisterVendor(string name, string street, string zipcode, string city, string country, string website, string kvknummer, string contactinitials, string contactinfix, string contactlastname, bool contactsex, string contactemail, string contactphone, string billingname, string billingcountry, string billingaccount, string billingstreet, string billingzipcode, string billingcity, string billingemail, string adminPassword, out uint newId)
		{
			QueryArguments args = new QueryArguments();

			args.Add("p_name", name);
			args.Add("p_street", street);
			args.Add("p_zipcode", zipcode);
			args.Add("p_city", city);
			args.Add("p_country", country);
			args.Add("p_website", website);
			args.Add("p_kvknummer", kvknummer);
			args.Add("p_contactinitials", contactinitials);
			args.Add("p_contactinfix", contactinfix);
			args.Add("p_contactlastname", contactlastname);
			args.Add("p_contactsex", contactsex);
			args.Add("p_contactemail", contactemail);
			args.Add("p_contactphone", contactphone);
			args.Add("p_billingname", billingname);
			args.Add("p_billingcountry", billingcountry);
			args.Add("p_billingaccount", billingaccount);
			args.Add("p_billingstreet", billingstreet);
			args.Add("p_billingzipcode", billingzipcode);
			args.Add("p_billingcity", billingcity);
			args.Add("p_billingemail", billingemail);
			args.Add("p_adminpassword", adminPassword);
			args.Add("p_vendorid", "").Direction = ParameterDirection.Output;

			Query query = new Query("sp_registervendor", args);
			
			object o = query.Execute();

			newId = Convert.ToUInt32(args[21].Value);

			return o.ToString();
		}

		internal static ResultSet GetInactiveVendors()
		{
			return (ResultSet)(new Query("sp_getinactivevendors").Execute());
		}

		internal static void ActivateVendor(ulong uid, string hashedPassword, ulong vendorid, int brancheId)
		{
			QueryArguments args = new QueryArguments();
			args.Add("p_userid", uid);
			args.Add("p_hashedpassword", hashedPassword);
			args.Add("p_vendorid", vendorid);
			args.Add("p_brancheid", brancheId);

			Query query = new Query("sp_activatevendor", args);

			query.Execute();

			ClearVendorCache((uint)vendorid);
		}

		internal static void InsertOffer(ulong uid, decimal credit, decimal price, int numberMax, TimeSpan expiresAfter, DateTime validFrom, DateTime validTo, string description)
		{
			QueryArguments args = new QueryArguments();
			args.Add("p_userid", uid);
			args.Add("p_credit", credit);
			args.Add("p_price", price);
			args.Add("p_numbermax", numberMax);
			args.Add("p_expiresafter", (int)expiresAfter.TotalHours);
			args.Add("p_validfrom", validFrom);
			args.Add("p_validto", validTo);
			args.Add("p_description", description);

			Query query = new Query("sp_createoffer", args);
			query.Execute();

			CacheConfig.Invalidate(CacheConfig.OffersChanged);
		}

		internal static ResultSet GetActiveOffers()
		{
			Query q = new Query("sp_getactiveoffers");

			return (ResultSet)q.Execute();
		}

		internal static ResultSet GetPayment(string transactionId)
		{
			Query q = new Query("sp_getpayment", "p_transactionid", transactionId);

			return (ResultSet)q.Execute();
		}

		internal static void SetPayed(string transactionId)
		{
			Query q = new Query("sp_setpayed", "p_transactionid", transactionId);

			q.Execute();
		}

		internal static void InsertDeposit(string transactionId, ulong uid, decimal amount, int type, DataTables.OrderBasketData orders)
		{
			QueryCollection queries = new QueryCollection();

			QueryArguments args = new QueryArguments();
			args.Add("p_transactionid", transactionId);
			args.Add("p_userid", uid);
			args.Add("p_amount", amount);
			args.Add("p_type", type);

			Query query = new Query("sp_insertdeposit", args);

			queries.Add(ReserveOffers(orders, uid, transactionId));
			queries.Add(query);
	
			queries.Execute();

			CacheConfig.Invalidate(CacheConfig.OffersChanged);
		}

		private static QueryCollection ReserveOffers(DataTables.OrderBasketData orders, ulong userId, string transactionId)
		{
			QueryCollection queries = new QueryCollection();

			foreach (DataTables.OrderBasketDataItem item in orders)
			{
				queries.Add(ReserveOffer(item.OfferId, item.Number, userId, transactionId));
			}

			return queries;
		}

		private static Query ReserveOffer(uint offerId, int number, ulong userId, string transactionId)
		{
			QueryArguments args = new QueryArguments();

			args.Add("p_offerid", offerId);
			args.Add("p_number", number);
			args.Add("p_userid", userId);
			args.Add("p_transactionid", transactionId);

			Query query = new Query("sp_reserveoffer", args);
			return query;
		}

		internal static void CancelTransaction(string transactionId)
		{
			Query query = new Query("sp_canceltransaction", "p_transactionid", transactionId);

			query.Execute();
		}

		internal static ResultSet GetTransactionOffers(string transactionId)
		{
			Query query = new Query("sp_gettransactionoffers", "p_transactionid", transactionId);

			return (ResultSet)query.Execute();
		}

		internal static void InsertDepositSaldoMutations(ResultSet depositPayedResultSet, uint userId, DateTime now)
		{
			QueryCollection queries = new QueryCollection();

			for (int i = 0; i < depositPayedResultSet.Count; i++)
			{
				DateTime validFrom = depositPayedResultSet.GetDateTime(i, "validfrom");
				if (validFrom < now)
					validFrom = now;

				int number = depositPayedResultSet.GetInt32(i, "number");
				decimal credit = depositPayedResultSet.GetDecimal(i, "credit");
				uint offerid = depositPayedResultSet.GetUInt32(i, "id");
				int expiresAfter = depositPayedResultSet.GetInt32(i, "expiresafter");

				DateTime expirationDate = validFrom.AddHours(Convert.ToDouble(expiresAfter));

				queries.Add(InsertSaldoMutationBatch(offerid, userId, number*credit, expirationDate));
			}

			queries.Execute();

			DataTables.MyTransactions.RemoveFromCache(userId);
			DataTables.UserActiveOfferdetailsPerVendor.RemoveFromCache(userId);
		}

		private static Query InsertSaldoMutationBatch(uint offerid, uint userid, decimal amount, DateTime? expirationdate)
		{
			QueryArguments args = new QueryArguments();

			args.Add("p_offerid", offerid);
			args.Add("p_userid", userid);
			args.Add("p_amount", amount);
			args.Add("p_expirationdate", expirationdate);

			Query query = new Query("sp_insertdepositsaldomutation", args);
			return query;
		}

		internal static void Pay(uint userid, uint vendorid, decimal amount, uint vendoruserid, string ww)
		{
			QueryArguments args = new QueryArguments();

			args.Add("p_userid", userid);
			args.Add("p_vendoruserid", vendoruserid);
			args.Add("p_vendorid", vendorid);
			args.Add("p_amount", amount);
			args.Add("p_pincode", ww);
	
			Query query = new Query("sp_pay", args);
			query.Execute();

			DataTables.MyTransactions.RemoveFromCache(userid);
			DataTables.UserActiveOfferdetailsPerVendor.RemoveFromCache(userid, vendorid);
		}

		internal static decimal GetUserCreditsAtVendor(uint userid, uint vendorid)
		{
			QueryArguments args = new QueryArguments();

			args.Add("p_userid", userid);
			args.Add("p_vendorid", vendorid);

			Query query = new Query("sp_getusercreditsatvendor", args);

			decimal credit = (decimal)query.Execute();

			return credit;
		}

		internal static ResultSet GetVendorOffers(UserInfo userInfo)
		{
			//get offers made by userId or all in case of administrator
			uint userId = (userInfo.Role == Role.Administrator ? 0xFFFFFFFF : userInfo.Id);

			QueryArguments args = new QueryArguments();

			args.Add("p_userid", userId);
			args.Add("p_vendorid", userInfo.VendorId);

			Query query = new Query("sp_getvendoroffers", args);

			return (ResultSet)query.Execute();
		}

		internal static ResultSet GetVendorOfferForEdit(UserInfo userInfo, uint offerId)
		{
			uint userId = (userInfo.Role == Role.Administrator ? 0xFFFFFFFF : userInfo.Id);

			QueryArguments args = new QueryArguments();

			args.Add("p_userid", userId);
			args.Add("p_vendorid", userInfo.VendorId);
			args.Add("p_offerid", offerId);

			Query query = new Query("sp_getvendorofferforedit", args);

			return (ResultSet)query.Execute();
		}

		internal static void UpdateOffer(uint offerId, int aantal, DateTime stopDatum)
		{
			QueryArguments args = new QueryArguments();

			args.Add("p_offerid", offerId);
			args.Add("p_numbermax", aantal);
			args.Add("p_validto", stopDatum);

			Query query = new Query("sp_updateoffer", args);

			query.Execute();

			CacheConfig.Invalidate(CacheConfig.OffersChanged);
		}

		internal static long CancelOffer(uint offerId)
		{
			Query query = new Query("sp_canceloffer", "p_offerid", offerId);

			long retval = (long)query.Execute();

			CacheConfig.Invalidate(CacheConfig.OffersChanged);

			return retval;
		}

		internal static long WithdrawOffer(uint offerId)
		{
			Query query = new Query("sp_withdrawoffer", "p_offerid", offerId);

			long retval = (long)query.Execute();

			CacheConfig.Invalidate(CacheConfig.OffersChanged);

			return retval;
		}

		internal static ResultSet GetNumberOfOffersPerVendor()
		{
			return (ResultSet)(new Query("sp_getnumberofofferspervendor").Execute());
		}

		internal static ResultSet GetActiveOffersPerVendor(uint vendorId)
		{
			return (ResultSet)(new Query("sp_getactiveofferspervendor", "p_vendorid", vendorId).Execute());
		}

		internal static ResultSet GetUserSaldi(uint userId)
		{
			return (ResultSet)(new Query("sp_getusersaldi", "p_userid", userId).Execute());
		}

		internal static ResultSet GetExpiredUserSaldi(uint userId)
		{
			return (ResultSet)(new Query("sp_getexpiredusersaldi", "p_userid", userId).Execute());
		}

		internal static ResultSet GetUserActiveOfferdetailsPerVendor(uint userId, uint vendorId)
		{
			QueryArguments args = new QueryArguments();

			args.Add("p_userid", userId);
			args.Add("p_vendorid", vendorId);

			Query query = new Query("sp_getuseractiveofferdetailspervendor", args);

			return (ResultSet)query.Execute();
		}

		internal static ResultSet GetUserHistory(uint userId)
		{
			return (ResultSet)(new Query("sp_getuserhistory", "p_userid", userId).Execute());
		}

		internal static ResultSet GetDepositsPerVendor(uint vendorId, DateTime dateFrom, DateTime dateTo)
		{
			QueryArguments args = new QueryArguments();

			args.Add("p_vendorid", vendorId);
			args.Add("p_datefrom", dateFrom);
			args.Add("p_dateto", dateTo);

			Query query = new Query("sp_getdepositspervendor", args);

			return (ResultSet)query.Execute();
		}

		internal static ResultSet GetDepositsPerVendorPerPeriod(uint vendorId, DateTime dateFrom, DateTime dateTo, string period)
		{
			QueryArguments args = new QueryArguments();

			args.Add("p_vendorid", vendorId);
			args.Add("p_datefrom", dateFrom);
			args.Add("p_dateto", dateTo);
			args.Add("p_period", period);

			Query query = new Query("sp_getdepositspervendorperperiod", args);

			return (ResultSet)query.Execute();
		}

		internal static ResultSet GetVendorTransactions(uint vendorId, DateTime dateFrom, DateTime dateTo)
		{
			QueryArguments args = new QueryArguments();

			args.Add("p_vendorid", vendorId);
			args.Add("p_datefrom", dateFrom);
			args.Add("p_dateto", dateTo);

			Query query = new Query("sp_getvendortransactions", args);

			return (ResultSet)query.Execute();
		}

		internal static ResultSet GetVendorCurrentBalance(uint vendorId, DateTime dateFrom, DateTime dateTo)
		{
			QueryArguments args = new QueryArguments();

			args.Add("p_vendorid", vendorId);
			args.Add("p_from", dateFrom);
			args.Add("p_to", dateTo);

			Query query = new Query("sp_getvendorcurrentbalance", args);

			return (ResultSet)query.Execute();
		}

		internal static ResultSet GetVendorUsers(uint vendorId)
		{
			return (ResultSet)(new Query("sp_getvendorusers", "p_vendorid", vendorId).Execute());
		}

		internal static long DeregisterUser(uint userId)
		{
			return (long)(new Query("sp_deregisteruser", "p_userid", userId).Execute());
		}

		internal static ResultSet GetDepositsForInvoice(int month, int year)
		{
			QueryArguments args = new QueryArguments();

			args.Add("p_month", month);
			args.Add("p_year", year);

			Query query = new Query("sp_getdepositsforinvoice", args);

			return (ResultSet)query.Execute();
		}

		internal static ResultSet GetPaymentsToVendor(uint vendorId, int month, int year)
		{
			QueryArguments args = new QueryArguments();

			args.Add("p_vendorid", vendorId);
			args.Add("p_month", month);
			args.Add("p_year", year);

			Query query = new Query("sp_getpaymentstovendor", args);

			return (ResultSet)query.Execute();
		}

		internal static void InsertPaymentToVendor(uint vendorId, int month, int year, decimal amount)
		{
			Query query = GetInsertPaymentToVendoryQuery(vendorId, month, year, amount);

			query.Execute();
		}

		internal static Query GetInsertPaymentToVendoryQuery(uint vendorId, int month, int year, decimal amount)
		{
			QueryArguments args = new QueryArguments();

			args.Add("p_vendorid", vendorId);
			args.Add("p_month", month);
			args.Add("p_year", year);
			args.Add("p_amount", amount);

			Query query = new Query("sp_insertpaymenttovendor", args);

			return query;
		}

		internal static Ajax.PaymentInfo GetAjaxPaymentInfo(uint userId, uint cashierId)
		{
			//Get user info's first, so they get cached wich makes the payment faster.
			UserInfo customer = GetUser((int)userId, true);
			if (customer == null)
				throw new Exception("Deze klant is niet bekend bij Gollie");

			UserInfo cashier = GetUser((int)cashierId, true);
			if (cashier == null)
				throw new Exception("Deze verkoper is niet bekend bij Gollie");

			decimal credit = GetUserCreditsAtVendor(userId, cashier.VendorId);

			return new Ajax.PaymentInfo()
			{
				CashierFullName = cashier.FullName,
				CustomerFullName = customer.FullName,
				Credit = credit,
				CreditString = credit.ToString("c2"),
				VendorName = cashier.VendorInfo.BedrijfsNaam,
				AllowRefund = cashier.VendorInfo.AllowRefund,
				ErrorCode = 0
			};
		}

		internal static ResultSetCollection GetLatestOffers()
		{
			return (ResultSetCollection)(new Query("sp_getlatestoffers").Execute());
		}

		internal static decimal GetTotalUserSaldo(uint userId)
		{
			Query query = new Query("sp_gettotalusersaldo", "p_userid", userId);

			object o = query.Execute();
			return Convert.ToDecimal(o);
		}

		internal static bool UserExists(uint userId, string password)
		{
			QueryArguments args = new QueryArguments();
			args.Add("p_userid", userId);
			args.Add("p_password", password);
			args.Add(new MySqlParameter("@p_retval", MySqlDbType.Int16) { Direction = ParameterDirection.ReturnValue }); 
			Query query = new Query("f_verifyuser", args);

			query.Execute();

			int o = Convert.ToInt32(args[2].Value);

			return (o != 0);
		}

		internal static ResultSet GetMails(DateTime from, DateTime to)
		{
			QueryArguments args = new QueryArguments();
			args.Add("p_datefrom", from);
			args.Add("p_dateto", to);

			Query query = new Query("sp_getmails", args);

			return (ResultSet)query.Execute();
		}

		internal static ResultSet GetUsersForMail(bool includeConsumers, bool includeVendors)
		{
			QueryArguments args = new QueryArguments();
			args.Add("p_includeconsumers", includeConsumers);
			args.Add("p_includevendors", includeVendors);

			Query query = new Query("sp_getusersformail", args);

			return (ResultSet)query.Execute();
		}

		internal static ResultSet GetReview(uint userId, uint vendorId, DateTime visitDate)
		{
			QueryArguments args = new QueryArguments();
			args.Add("p_userid", userId);
			args.Add("p_vendorid", vendorId);
			args.Add("p_visitdate", visitDate);

			Query query = new Query("sp_getreview", args);

			return (ResultSet)query.Execute();
		}

		internal static void InsertReview(uint userId, uint vendorId, DateTime visitDate, uint rating, string comment)
		{
			QueryArguments args = new QueryArguments();
			args.Add("p_userid", userId);
			args.Add("p_vendorid", vendorId);
			args.Add("p_visitdate", visitDate);
			args.Add("p_rating", rating);
			args.Add("p_comment", comment);

			Query query = new Query("sp_insertreview", args);

			query.Execute();
		}

		internal static ResultSet GetReviews(uint vendorId, DateTime from, DateTime to)
		{
			QueryArguments args = new QueryArguments();
			args.Add("p_vendorid", vendorId);
			args.Add("p_from", from);
			args.Add("p_to", to);

			Query query = new Query("sp_getreviews", args);

			return (ResultSet)query.Execute();
		}

		internal static double GetAvgRating(uint vendorId, DateTime from, DateTime to)
		{
			QueryArguments args = new QueryArguments();
			args.Add("p_vendorid", vendorId);
			args.Add("p_from", from);
			args.Add("p_to", to);

			Query query = new Query("sp_getavgrating", args);

			object o = query.Execute();
			if (o == null)
				return 0;

			return (double)o;
		}

		internal static ResultSetCollection GetLatestCashClose(uint vendorUserId)
		{
			return (ResultSetCollection)(new Query("sp_getlatestcashclose", "p_vendoruserid", vendorUserId).Execute());
		}

		internal static void InsertCashClose(uint vendorUserId, string ww)
		{
			QueryArguments args = new QueryArguments();
			args.Add("p_vendoruserid", vendorUserId);
			args.Add("p_password", ww);

			Query query = new Query("sp_insertcashclose", args);

			query.Execute();
		}

		internal static void DeleteCashClose(uint vendorUserId, string ww)
		{
			QueryArguments args = new QueryArguments();
			args.Add("p_vendoruserid", vendorUserId);
			args.Add("p_password", ww);

			Query query = new Query("sp_deletelatestcashclose", args);

			query.Execute();
		}

		internal static string InsertPendingPayment(uint vendorUserId, decimal amount)
		{
			QueryArguments args = new QueryArguments();
			args.Add("p_vendoruserid", vendorUserId);
			args.Add("p_amount", amount);

			Query query = new Query("sp_insertpendingpayment", args);

			string code = (string)query.Execute();

			return code;
		}

		internal static ResultSet GetPendingPayment(uint vendorId, uint consumerId, string verificationCode)
		{
			QueryArguments args = new QueryArguments();
			args.Add("p_vendorid", vendorId);
			args.Add("p_consumerid", consumerId);
			args.Add("p_verificationcode", verificationCode);

			Query query = new Query("sp_getpendingpayment", args);

			return (ResultSet)query.Execute();
		}

		internal static ResultSet FinalizePendingPayment(uint vendorId, uint consumerId, string verificationCode, string pincode, ushort action)
		{
			QueryArguments args = new QueryArguments();
			args.Add("p_vendorid", vendorId);
			args.Add("p_consumerid", consumerId);
			args.Add("p_verificationcode", verificationCode);
			args.Add("p_pincode", pincode);
			args.Add("p_action", action);

			Query query = new Query("sp_finalizependingpayment", args);

			ResultSet r = (ResultSet)query.Execute();

			if (action != 0) //not disapproved? Remove saldomutation cache
				Gollie.DataTables.UserActiveOfferdetailsPerVendor.RemoveFromCache(consumerId, vendorId);

			return r;
		}

		internal static ResultSet CheckPendingPayment(uint vendorId, string verificationCode)
		{
			QueryArguments args = new QueryArguments();
			args.Add("p_vendorid", vendorId);
			args.Add("p_verificationcode", verificationCode);

			Query query = new Query("sp_checkpendingpayment", args);

			return (ResultSet)query.Execute();
		}

		internal static bool KvKExists(string kvk)
		{
			long res = (long)(new Query("sp_kvkexists", "p_kvk", kvk).Execute());

			return (res == 1);
		}

		internal static ResultSet GetInvoices(uint vendorId)
		{
			return (ResultSet)(new Query("sp_getinvoices", "p_vendorid", vendorId).Execute());
		}
		
		internal static int CountUnsentMails()
		{
			return (int)(long)(new Query("sp_countunsentmails").Execute());
		}

		internal static ResultSet GetUnsentMails()
		{
			return new Query("sp_getunsentmails").Execute() as ResultSet;
		}
    }
}